﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
///Order 的摘要说明
/// </summary>
public class Order : Component
{
    private string orderID = "";
    private Agent agent = new Agent(new User());
    private Hotel hotel = new Hotel();
    private string starttime = "";

    private string endtime = "";
    
    private double totalprice = 0;
    private List<Room> roomList = new List<Room>();
    private List<Student> studenList = new List<Student>();
    private List<RoomTypeNum> roomtypenum = new List<RoomTypeNum>();


    public string OrderID
    {
        get { return orderID; }
        set { orderID = value; }
    }
    

    public Agent Agent
    {
        get { return agent; }
        set { agent = value; }
    }
    

    public Hotel Hotel
    {
        get { return hotel; }
        set { hotel = value; }
    }
    
    public string Starttime
    {
        get { return starttime; }
        set { starttime = value; }
    }
  

    public string Endtime
    {
        get { return endtime; }
        set { endtime = value; }
    }


    public List<Student> StudenList
    {
        get { return studenList; }
        set { studenList = value; }
    }

    public List<Room> RoomList
    {
        get { return roomList; }
        set { roomList = value; }
    }

    public double Totalprice
    {
        get { return totalprice; }
        set { totalprice = value; }
    }



    public List<RoomTypeNum> Roomtypenum
    {
        get { return roomtypenum; }
        set { roomtypenum = value; }
    }









    override public string insert(System.Data.SqlClient.SqlConnection sqlcon)
    {
        //try
        {
            if (orderID == null)
            {
                return null;
            }
            SqlDataAdapter sqladt = new SqlDataAdapter();

            //录入学生信息,其可保留,所以可以先录入以致不会导致冲突

            SqlFormat.beginTran(sqlcon);
            foreach (Student student in studenList)
            {
                // string testname = " SELECT IDcard FROM Student WHERE IDcard=" + ap(student.IDCard1) +";";
                string cmdselStudent = " SELECT IDcard FROM Student WHERE IDcard=" + SqlFormat.ap(student.IDCard1) + ";";
                /*+"AND sex="+student.Sex
                +" AND name="+ap(student.Name)
                +" AND studentID="+ap(student.StudentID)
                + " AND SchoolId=" +agent.SchoolID + ";";*/

                DataTable dts = new DataTable();

                sqladt.SelectCommand = new SqlCommand(cmdselStudent, sqlcon);

                sqladt.Fill(dts);



                if (dts.Rows.Count == 0) //如果学生信息不存在，需要插入
                {
                    /*  sqladt.SelectCommand = new SqlCommand(cmdselStudent, sqlcon);
                      sqladt.Fill(dts);
                      //检查名字合法性
                      if (dts.Rows.Count > 0)
                      {
                          SqlFormat.rollback(sqlcon);
                          return "<script language=javascript > alert('提交出错，学生信息有误！');</script>";
                      }*/



                    if (student.insert(sqlcon) == null)  //插入不成功
                    {

                        SqlFormat.rollback(sqlcon);
                        return "<script language=javascript > alert('提交出错，学生信息无法录入！');</script>";
                    }

                }

            }
            //插入订单后信息后需要提交事务，否则无法录入订单学生表
            SqlFormat.commit(sqlcon);



            string[] ordervalues = { SqlFormat.ap(orderID),
                                 SqlFormat.ap(Agent.AgentID), 
                                SqlFormat.ap( Hotel.HotelID),
                                 SqlFormat.ap(starttime), 
                                 SqlFormat.ap(endtime),
                                 SqlFormat.ap(DateTime.Now.ToString()),
                               ""+1};

            string cmdinOrderTable = "begin tran; " + SqlFormat.exeProcedureNotap("inputOrderTable", ordervalues);
            sqladt.InsertCommand = new SqlCommand(cmdinOrderTable, sqlcon);

            //预定的时间
            DateTime orderStartTimeOrigin;
            DateTime orderEndTimeOrigin;
            DateTime.TryParse(starttime, out orderStartTimeOrigin);
            DateTime.TryParse(endtime, out orderEndTimeOrigin);

            
            //录入订单表成功
            if (sqladt.InsertCommand.ExecuteNonQuery() >= 0)
            {
                //检查房间是否可用，并保存至房间列表,并且更新数据库


                RoomList.Clear();//列表的room


                if (Roomtypenum.Count == 0)
                {
                    return "<script language=javascript > alert('没有房间输入数量！');</script>";
                }

                foreach (RoomTypeNum rn in Roomtypenum)
                {
                    string freeRoomwhere = "WHERE hotelID="
                         + SqlFormat.ap(Hotel.HotelID)
                         + " AND roomsize=" + rn.Size
                         + " AND startfreetime<="
                         + SqlFormat.ap(starttime)
                         + " AND endfreetime>="
                         + SqlFormat.ap(endtime)
                         + " AND freestate=" + 0;


                    string sql = " SELECT hotelID ,roomname , startfreetime ,endfreetime ,roomsize ,price ,freestate  FROM RoomTable  " 
                        + freeRoomwhere + "order by startfreetime desc,endfreetime asc ;";

                    sqladt.SelectCommand = new SqlCommand(sql, sqlcon);
                    DataTable dt = new DataTable();
                    sqladt.Fill(dt);
                    if (dt.Rows.Count < rn.Num) //满足的房间数量不足，回退事务
                    {
                        SqlFormat.rollback(sqlcon);


                        return "<script language=javascript > alert('符合条件的房间数量不足！');</script>";
                    }

                  

                    //记录可用的房间，同时更新空余房间
                    for (int i = 0; i < rn.Num; i++)
                    {


                        DataRow dr = dt.Rows[i];


                        string Name = dr[1].ToString();
                        string Size = dr[4].ToString();
                        string Starttime = dr[2].ToString();
                        string Endtime = dr[3].ToString();
                        string Price = dr[5].ToString();

                        //房间空余的时间
                        DateTime roomFreeStartTime;
                        DateTime roomFreeEndTime;
                        DateTime.TryParse(Starttime, out roomFreeStartTime);
                        DateTime.TryParse(Endtime, out roomFreeEndTime);
                        string freeRoomwhereup = "WHERE hotelID="
                                                   + SqlFormat.ap(Hotel.HotelID)
                                                   + " AND roomname=" + SqlFormat.ap(Name)
                                                   + " AND roomsize=" + rn.Size
                                                   + " AND startfreetime="
                                                   + SqlFormat.ap(Starttime)
                                                   + " AND endfreetime="
                                                   + SqlFormat.ap(Endtime)
                                                   + " AND freestate=" + 0;

                        //修改日期完全符合的空余房间，并且添加到订单列表
                        if (DateTime.Compare(orderStartTimeOrigin, roomFreeStartTime) == 0 && DateTime.Compare(orderEndTimeOrigin, roomFreeEndTime) == 0)
                        {



                            string sqlupd = "UPDATE RoomTable SET freestate="
                                                    + 1
                                //+ ", startfreetime=" + ap(starttime)
                                //+ ", endfreetime=" + ap(endtime)
                                                    + freeRoomwhereup + ";";

                            sqladt.UpdateCommand = new SqlCommand(sqlupd, sqlcon);
                            if (sqladt.UpdateCommand.ExecuteNonQuery() == 0)
                            {
                                SqlFormat.rollback(sqlcon);

                                return "<script language=javascript > alert('提交出错，无法修改空闲信息！');</script>";
                            }
                        }
                        //如果时间不完全对应的话，需要修改插入新的空闲时间
                        //预订开始时间！=房间空闲开始时间，预订结束时间！=房间空闲结束
                        else
                        {

                            //先修改这个房间~再插入= =
                            string sqlupd = "UPDATE RoomTable SET freestate="
                                                    + 1
                                                    + ", startfreetime=" + SqlFormat.ap(starttime)
                                                    + ", endfreetime=" + SqlFormat.ap(endtime)
                                                    + freeRoomwhereup + ";";
                            sqladt.UpdateCommand = new SqlCommand(sqlupd, sqlcon);
                            if (sqladt.UpdateCommand.ExecuteNonQuery() == 0)
                            {
                                SqlFormat.rollback(sqlcon);

                                return "<script language=javascript > alert('提交出错，无法修改空闲信息！');</script>";
                            }
                            //1 插入:  空闲开始=空闲原来开始，空闲结束=预定开始-1天------  R-[起始，结束)
                            //2 插入:  空闲开始=预定结束+1天，空闲结束=原来空闲结束------- R-（起始，结束]
                            //3 插入： (空闲开始=原来开始，空闲结束=预订-1天)，(空闲开始=预订结束+1，空闲结束=原来结束)-----------  R-(起始，结束)  

                            DateTime orderStartTimeCopy, orderEndTimeCopy;//预订开始时间副本，结束副本
                            DateTime.TryParse(starttime, out orderStartTimeCopy);
                            DateTime.TryParse(endtime, out orderEndTimeCopy);

                            bool stateEqualsStart = true;  //预定起始时间与空闲起始时间相等的情况下
                            bool stateEqualsEnd = true;  //预定结束时间与空闲结束时间相等的情况下
                            if (DateTime.Compare(orderStartTimeOrigin, roomFreeStartTime) != 0)
                            {
                                stateEqualsStart = false;
                                orderStartTimeCopy = orderStartTimeCopy.AddDays(-1);
                            }
                            if (DateTime.Compare(orderEndTimeOrigin, roomFreeEndTime) != 0)
                            {
                                stateEqualsEnd = false;
                                orderEndTimeCopy = orderEndTimeCopy.AddDays(1);
                            }






                            string[] valueNoEqualsStart = { SqlFormat.ap(Hotel.HotelID), SqlFormat.ap(Name), SqlFormat.ap(Starttime), SqlFormat.ap(orderStartTimeCopy.ToShortDateString()), Size, Price, "" + 0 };
                            string[] valueNoEqualsEnd = { SqlFormat.ap(Hotel.HotelID), SqlFormat.ap(Name), SqlFormat.ap(orderEndTimeCopy.ToShortDateString()), SqlFormat.ap(Endtime), Size, Price, "" + 0 };

                            string sqlinNoEqualsStart = SqlFormat.insertTableNotAp("RoomTable", valueNoEqualsStart);
                            string sqlinNoEqualsEnd = SqlFormat.insertTableNotAp("RoomTable", valueNoEqualsEnd);


                            sqladt.InsertCommand = new SqlCommand(sqlinNoEqualsEnd, sqlcon);
                            if ((stateEqualsStart == true && stateEqualsEnd == false) || (stateEqualsStart == false && stateEqualsEnd == false))
                                if (sqladt.InsertCommand.ExecuteNonQuery() == 0)
                                {

                                    SqlFormat.rollback(sqlcon);

                                    return "<script language=javascript > alert('提交出错，无法录入房间订单！');</script>";
                                }

                            sqladt.InsertCommand = new SqlCommand(sqlinNoEqualsStart, sqlcon);
                            if ((stateEqualsEnd == true && stateEqualsStart == false) || (stateEqualsStart == false && stateEqualsEnd == false))
                                if (sqladt.InsertCommand.ExecuteNonQuery() == 0)
                                {

                                    SqlFormat.rollback(sqlcon);
                                    return "<script language=javascript > alert('提交出错，无法录入房间订单！');</script>";

                                }



                        }


                        //插入订单房间列表
                        string[] orderroomvalue = { orderID, Name, Size, Price };
                        //string sqlin = SqlFormat.exeProcedure("inputOrderRoom", orderroomvalue);
                        string sqlin = SqlFormat.insertTable("OrderRoom", orderroomvalue);
                        sqladt.InsertCommand = new SqlCommand(sqlin, sqlcon);
                        if (sqladt.InsertCommand.ExecuteNonQuery() == 0)
                        {

                            SqlFormat.rollback(sqlcon);
                            return "<script language=javascript > alert('提交出错，无法录入订单的房间！');</script>";
                        }

                        //讲房间信息存入session
                        Room room = new Room();
                        room.Name = Name;
                        room.Size = Convert.ToInt32(Size);
                        room.Price = Convert.ToDouble(Price);
                        RoomList.Add(room);

                        TimeSpan orderDayLong = orderStartTimeOrigin - orderEndTimeOrigin;

                        double daylong=orderDayLong.Days;
                        if (daylong < 0)
                            daylong = 0;
                        if (daylong == 0)
                        {
                            daylong = 1;
                        }

                        Totalprice = Totalprice + (Convert.ToDouble(Price) * daylong);

                    }




                }



                //录入学生订单信息
                foreach (Student student in studenList)
                {


                    string[] osvalues = { orderID, student.IDCard1 };
                    //string sqlinsostu =SqlFormat.exeProcedure("inputOrderStudent", osvalues);
                    string sqlinsostu = SqlFormat.insertTable("OrderStudent", osvalues);

                    sqladt.InsertCommand = new SqlCommand(sqlinsostu, sqlcon);
                    if (sqladt.InsertCommand.ExecuteNonQuery() == 0)  //插入不成功
                    {

                        SqlFormat.rollback(sqlcon);
                        return "<script language=javascript > alert('提交出错，订单与学生信息无法录入！');</script>";
                    }


                }

                //提交完成一个订单的事务
                SqlFormat.commit(sqlcon);



            }
            else
            {

                string back = "rollback tran ;";
                sqladt.SelectCommand = new SqlCommand(back, sqlcon);
                sqladt.SelectCommand.ExecuteNonQuery();
                return "<script language=javascript > alert('提交出错');</script>";
            }
            return "ok";
        }
       // catch (System.Exception ex)
      //  {
   //         return ex.Message;
     //   }
       
    }

	public Order()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}





    public static String cancel(SqlConnection sqlcon,String OrderID)
    {
       //首先应获取预订的时间和宾馆ID
        try
        {
            SqlDataAdapter sqldapt = new SqlDataAdapter();
            DataTable datetable = new DataTable();


            string sqlorder = "begin tran; SELECT * FROM ORDERTABLE WHERE ORDERID=" + SqlFormat.ap(OrderID) + " AND orderstatus=1";
            sqldapt.SelectCommand = new SqlCommand(sqlorder, sqlcon);
            sqldapt.Fill(datetable);
            if (datetable.Rows.Count != 1)
            {
                return "订单无需取消！";
            }

            String HotelID = datetable.Rows[0].ItemArray[2].ToString();
            String Starttime = datetable.Rows[0].ItemArray[3].ToString();
            string Endtime = datetable.Rows[0].ItemArray[4].ToString();

            DateTime orderstarttime, orderendtime; //预订起始、结束时间

            //  DateTime.TryParse(datetable.Rows[0].ItemArray[3].ToString(), out orderstarttime);
            // DateTime.TryParse(datetable.Rows[0].ItemArray[4].ToString(), out orderendtime);
            DateTime.TryParse(Starttime, out orderstarttime);
            DateTime.TryParse(Endtime, out orderendtime);


            // String hotelID = datetable.Rows[0].ItemArray[2].ToString();


            //获取订单房间表中与订单ID 相符的房间，即查询订单占用的房间
            string selroom = "SELECT * FROM ORDERROOM WHERE ORDERID=" + SqlFormat.ap(OrderID);


            sqldapt.SelectCommand = new SqlCommand(selroom, sqlcon);

            datetable = new DataTable();

            sqldapt.Fill(datetable);

            //然后循环，在空余房间表找出 (空闲时间末端=预订开始时间-1 OR 空闲开始时间=预订结束时间+1) 的房间
            //若结果有两个，说明时间片段刚好连续，记录前后时间，删除这两个房间，修改占用的房间状态和时间
            //若有一个，说明存在可拼接的一个时间片，删除这个时间，修改占用的房间状态和时间
            //若不存在，说明不存在连续的时间片，修改该占用房间的状态为空闲

            foreach (DataRow row in datetable.Rows)
            {//此处循环为寻找占用的房间
                //结果：订单ID，房间名，大小，价格



                DataTable da1 = new DataTable();



                //查询房间表
                string selsql = "SELECT * FROM RoomTable WHERE hotelID=" + SqlFormat.ap(HotelID)
                                + " AND  roomname=" + SqlFormat.ap(row.ItemArray[1].ToString())
                                + " AND roomsize=" + row.ItemArray[2].ToString();


                //首先要在房间表中找出该占用房间的信息
                string sqland = "AND startfreetime=" + SqlFormat.ap(Starttime)
                               + "AND endfreetime=" + SqlFormat.ap(Endtime);

                sqldapt.SelectCommand = new SqlCommand(selsql + sqland, sqlcon);
                sqldapt.Fill(da1);

                if (da1.Rows.Count != 1)
                {
                    string back = "rollback tran ;";
                    sqldapt.SelectCommand = new SqlCommand(back, sqlcon);
                    sqldapt.SelectCommand.ExecuteNonQuery();

                    return "占用房间查询出错！";
                }



                Room takeuproom = new Room();
                takeuproom.setData(da1.Rows[0]);


                string timesql1 = " AND (endfreetime=" + SqlFormat.ap((orderstarttime.AddDays(-1)).ToShortDateString())
                                + " OR startfreetime=" + SqlFormat.ap((orderendtime.AddDays(1)).ToShortDateString()) + ") ORDER BY startfreetime ASC;";


                da1 = new DataTable();
                sqldapt.SelectCommand = new SqlCommand(selsql + timesql1, sqlcon);
                sqldapt.Fill(da1);
                if (da1.Rows.Count == 2) //若找到2条记录，说明存在可以符合拼接的
                {
                    //。查询为开始时间升序，即第一个时间较早


                    DataRow row1 = da1.Rows[0];
                    DataRow row2 = da1.Rows[1];

                    //删除两个时间
                    Room room1 = new Room();
                    room1.setData(row1);
                    Room room2 = new Room();
                    room2.setData(row2);

                    //
                    Room roomnew = takeuproom.Clone();
                    roomnew.Starttime = room1.Starttime;
                    roomnew.Endtime = room2.Endtime;
                    roomnew.Freestate = "0";
                    //修改时间


                    if (room1.deleteSql(sqlcon) == false)
                    {
                        SqlFormat.rollback(sqlcon);

                        return "删除1：1出错";
                    }

                    if (room2.deleteSql(sqlcon) == false)
                    {
                        SqlFormat.rollback(sqlcon);
                        return "删除1：2出错";
                    }

                    if (takeuproom.updateSql(sqlcon, roomnew) == false)
                    {
                        SqlFormat.rollback(sqlcon);
                        return "修改1:1失败！";
                    }

                }
                else if (da1.Rows.Count == 1)
                {
                    //判断是可以拼接到该时间段末尾还是首部，删除该段，修改占用的房间


                    DataRow rowtmp = da1.Rows[0];
                    Room room = new Room();
                    room.setData(rowtmp);


                    if (room.deleteSql(sqlcon) == false)
                    {
                        SqlFormat.rollback(sqlcon);
                        return "删除2：1出错";
                    }

                    DateTime tmpstarttime, tmpendtime;//空闲房间的时间  （-，起始），（结束，+）

                    DateTime.TryParse(room.Starttime, out tmpstarttime);
                    DateTime.TryParse(room.Endtime, out tmpendtime);

                    if (DateTime.Compare(tmpendtime, orderstarttime) == 0)//可以连接到该末端
                    {
                        Room roomnew = takeuproom.Clone();
                        roomnew.Starttime = room.Starttime;
                        roomnew.Freestate = "0";
                        if (takeuproom.updateSql(sqlcon, roomnew) == false)
                        {
                            SqlFormat.rollback(sqlcon);
                            return "修改2:1失败！";
                        }
                    }
                    else if (DateTime.Compare(tmpstarttime, orderendtime) == 0)//可以连接到起始
                    {
                        Room roomnew = takeuproom.Clone();
                        roomnew.Endtime = room.Endtime;
                        roomnew.Freestate = "0";
                        if (takeuproom.updateSql(sqlcon, roomnew) == false)
                        {
                            SqlFormat.rollback(sqlcon);
                            return "修改2:2失败！";
                        }
                    }
                    else
                    {
                        return "修改2:3失败！";
                    }






                }
                else if (da1.Rows.Count == 0)
                {
                    //找不到可以拼接的时间段，就直接将状态修改成空闲
                    Room roomnew = takeuproom.Clone();
                    roomnew.Freestate = "0";
                    //修改时间
                    takeuproom.updateSql(sqlcon, roomnew);


                }





            }

            string ordersql = "UPDATE ORDERTABLE SET ORDERSTATUS=0 WHERE ORDERID=" + SqlFormat.ap(OrderID);
            sqldapt.UpdateCommand = new SqlCommand(ordersql, sqlcon);
            if (sqldapt.UpdateCommand.ExecuteNonQuery() != 1)
                return "修改订单状态失败";


            SqlFormat.commit(sqlcon);

            return "订单取消成功";
        }
        catch (System.Exception ex)
        {
            return ex.Source;
        }
       
    }
}